import mariadb,os,sys

class SQLHelper:
    TABLE_FUND_BASEINFO= "fund_baseinfo"
    TABLE_FUND_FILEINFO= "fund_profilinfo"
    TABLE_FUND_RISKINFO= "fund_riskinfo"
        
    def __init__(self, database, hostaddr, username, pwd):
        try:
            self.conn = mariadb.connect(
                user=username,
                password=pwd,
                host=hostaddr,
                port=3306,
                database=database)
            self.__createFundBaseInfoTable()
            self.__createFundFileInfoTable()
            self.__createFundRiskInfoTable()
        except mariadb.Error as e:
            print("Error connecting to MariaDB Platform: {}".format(str(e)))
            sys.exit(1)

    def __createFundBaseInfoTable(self):
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS {} (ID INTEGER PRIMARY KEY AUTO_INCREMENT, CODE VARCHAR(50), MSCODE VARCHAR(100), NAME VARCHAR(200), FUNDTYPE VARCHAR(100), THREERATING INT, FIVERATING INT, RATEOFRETURN FLOAT);".format(SQLHelper.TABLE_FUND_BASEINFO))
        cur.close()

    def __createFundFileInfoTable(self):
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS {} (ID INTEGER PRIMARY KEY AUTO_INCREMENT, MSCODE VARCHAR(100), TIME VARCHAR(20), MINUNIT VARCHAR(10), ASSET FLOAT, SBDESC VARCHAR(20), FORNT VARCHAR(10), DEFER VARCHAR(10), REDEEM VARCHAR(10), SUBSCRIBE VARCHAR(10), PROFILE VARCHAR(200));".format(SQLHelper.TABLE_FUND_FILEINFO))
        cur.close()

    def __createFundRiskInfoTable(self):
        cur = self.conn.cursor()
        cur.execute("CREATE TABLE IF NOT EXISTS {} (ID INTEGER PRIMARY KEY AUTO_INCREMENT, MSCODE VARCHAR(100), ALPHA FLOAT, BETA FLOAT, RSQUARED FLOAT, RR FLOAT, SD FLOAT, MSINDEX FLOAT, SHARPERATIO FLOAT);".format(SQLHelper.TABLE_FUND_RISKINFO))
        cur.close()

    def __addNewFundBaseInfo(self, code, mscode, name, fundtype, threerating, fiverating, rof):
        cur = self.conn.cursor()
        cur.execute("INSERT INTO {} (CODE, MSCODE, NAME, FUNDTYPE, THREERATING, FIVERATING, RATEOFRETURN) VALUES ('{}', '{}', '{}', '{}', {}, {}, {});".format(SQLHelper.TABLE_FUND_BASEINFO, code, mscode, name, fundtype, threerating, fiverating, rof))
        self.conn.commit()
        cur.close()
    
    def __addNewFundFileInfo(self, mscode, time, minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile):
        cur = self.conn.cursor()
        cur.execute("INSERT INTO {} (MSCODE, TIME, MINUNIT, ASSET, SBDESC, FORNT, DEFER, SUBSCRIBE, REDEEM, PROFILE) VALUES ('{}', '{}', '{}', {}, '{}', '{}', '{}', '{}', '{}', '{}');".format(SQLHelper.TABLE_FUND_FILEINFO, mscode, time,  minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile))
        self.conn.commit()
        cur.close()

    def __addNewFundRiskInfo(self, mscode, alpha, beta, rsquared, rr, sd, msindex, sharperatio):
        cur = self.conn.cursor()
        cur.execute("INSERT INTO {} (MSCODE, ALPHA, BETA, RSQUARED, RR, SD, MSINDEX, SHARPERATIO) VALUES ('{}', {},  {}, {}, {}, {}, {}, {});".format(SQLHelper.TABLE_FUND_RISKINFO, mscode, alpha, beta, rsquared, rr, sd, msindex, sharperatio))
        self.conn.commit()
        cur.close()

    def __getFundBaseInfo(self, code):
        cur = self.conn.cursor()
        cur.execute("SELECT ID, CODE, MSCODE, NAME, FUNDTYPE, THREERATING, FIVERATING, RATEOFRETURN FROM {} WHERE CODE=?;".format(SQLHelper.TABLE_FUND_BASEINFO), (code,))
        info = cur.fetchone()
        cur.close()
        return info

    def __getAllMSCode(self, threerating, fiverating):
        cur = self.conn.cursor()
        cur.execute("SELECT MSCODE FROM {} WHERE THREERATING {} AND FIVERATING {};".format(SQLHelper.TABLE_FUND_BASEINFO, threerating, fiverating))
        info = cur.fetchall()
        cur.close()
        return info

    def __getFundFileInfo(self, mscode):
        cur = self.conn.cursor()
        cur.execute("SELECT ID, TIME, MINUNIT, ASSET, SBDESC, FORNT, DEFER, SUBSCRIBE, REDEEM, PROFILE FROM {} WHERE MSCODE=?;".format(SQLHelper.TABLE_FUND_FILEINFO), (mscode,))
        info = cur.fetchone()
        cur.close()
        return info

    def __getFundRiskInfo(self, mscode):
        cur = self.conn.cursor()
        cur.execute("SELECT ID, ALPHA, BETA, RSQUARED, RR, SD, MSINDEX, SHARPERATIO FROM {} WHERE MSCODE=?;".format(SQLHelper.TABLE_FUND_RISKINFO), (mscode,))
        info = cur.fetchone()
        cur.close()
        return info

    def __updateFundBaseInfo(self, id, code, mscode, name, fundtype, threerating, fiverating, rof):
        cur = self.conn.cursor()
        cur.execute("UPDATE {} SET CODE='{}', MSCODE='{}', NAME='{}', FUNDTYPE='{}', THREERATING={}, FIVERATING={}, RATEOFRETURN={} WHERE ID={};".format(SQLHelper.TABLE_FUND_BASEINFO,  code, mscode, name, fundtype, threerating, fiverating, rof, id))
        self.conn.commit()
        cur.close() 
    
    def __updateFundFileInfo(self, id, time, minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile):
        cur = self.conn.cursor()
        cur.execute("UPDATE {} SET TIME='{}', MINUNIT='{}', ASSET={}, SBDESC='{}', FORNT='{}', DEFER='{}', SUBSCRIBE='{}', REDEEM='{}', PROFILE='{}' WHERE ID={};".format(SQLHelper.TABLE_FUND_FILEINFO, time, minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile, id))
        self.conn.commit()
        cur.close() 

    def __updateFundRiskInfo(self, id, alpha, beta, rsquared, rr, sd, msindex, sharperatio):
        cur = self.conn.cursor()
        cur.execute("UPDATE {} SET ALPHA={}, BETA={}, RSQUARED={}, RR={}, SD={}, MSINDEX={}, SHARPERATIO={} WHERE ID={};".format(SQLHelper.TABLE_FUND_RISKINFO, alpha, beta, rsquared, rr, sd, msindex, sharperatio, id))
        self.conn.commit()
        cur.close() 
    
    def updateFundBaseInfo(self, code, mscode, name, fundtype, threerating, fiverating, rof):
        info = self.__getFundBaseInfo(code)
        if(info):
            if(info[1] != code or info[2] != mscode or info[3] != name or info[4] != fundtype or info[5] != threerating or info[6] != fiverating or info[7] != rof):
                self.__updateFundBaseInfo(info[0], code, mscode, name, fundtype, threerating, fiverating, rof)
        else:
            self.__addNewFundBaseInfo(code, mscode, name, fundtype, threerating, fiverating, rof)

    def updateFundFileInfo(self, mscode, time, minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile):
        info = self.__getFundFileInfo(mscode)
        if(info):
            if(info[1] != time or info[2] != minunit  or info[3] != asset or info[4] != sbdesc or info[5] != fornt or info[6] != defer or info[7] != subscribe or info[8] != redeem or info[9] != profile):
                self.__updateFundFileInfo(info[0], time, minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile)
        else:
            self.__addNewFundFileInfo(mscode, time, minunit, asset, sbdesc, fornt, defer, subscribe, redeem, profile)

    def updateFundRiskInfo(self, mscode, alpha, beta, rsquared, rr, sd, msindex, sharperatio):
        info = self.__getFundRiskInfo(mscode)
        if(info):
            if(info[1] != alpha or info[2] != beta or info[3] != rsquared or info[4] != rr  or info[5] != sd or info[6] != msindex or info[7] != sharperatio):
                self.__updateFundRiskInfo(info[0], alpha, beta, rsquared, rr, sd, msindex, sharperatio)
        else:
            self.__addNewFundRiskInfo(mscode, alpha, beta, rsquared, rr, sd, msindex, sharperatio)

    def getAllMSCode(self, mscodelist, three, five):
        info = self.__getAllMSCode(three, five)
        if(info):
            for data in info:
                mscodelist.append(data[0])
        return mscodelist
    
    def getTopFunds(self, threerating, fiverating, beta, count):
        cur = self.conn.cursor()
        print("SELECT {0}.CODE, {0}.MSCODE, {0}.NAME, {0}.FUNDTYPE, {1}.FORNT, {1}.REDEEM, {1}.SUBSCRIBE, {0}.RATEOFRETURN, {0}.THREERATING, {0}.FIVERATING, {1}.MINUNIT, {2}.BETA FROM {0} INNER JOIN {1} ON {0}.MSCODE = {1}.MSCODE INNER JOIN {2} ON {0}.MSCODE = {2}.MSCODE AND {0}.THREERATING {3} AND {0}.FIVERATING {4} AND {2}.BETA <> 1029 ORDER BY {2}.RSQUARED DESC, {2}.ALPHA DESC, {2}.BETA {5} LIMIT {6};".format(SQLHelper.TABLE_FUND_BASEINFO, SQLHelper.TABLE_FUND_FILEINFO, SQLHelper.TABLE_FUND_RISKINFO, threerating, fiverating,  beta, count))
        cur.execute("SELECT {0}.CODE, {0}.MSCODE, {0}.NAME, {0}.FUNDTYPE, {1}.FORNT, {1}.REDEEM, {1}.SUBSCRIBE, {0}.RATEOFRETURN, {0}.THREERATING, {0}.FIVERATING, {1}.MINUNIT, {2}.BETA FROM {0} INNER JOIN {1} ON {0}.MSCODE = {1}.MSCODE INNER JOIN {2} ON {0}.MSCODE = {2}.MSCODE AND {0}.THREERATING {3} AND {0}.FIVERATING {4} AND {2}.BETA <> 1029 ORDER BY {2}.RSQUARED DESC, {2}.ALPHA DESC, {2}.BETA {5} LIMIT {6};".format(SQLHelper.TABLE_FUND_BASEINFO, SQLHelper.TABLE_FUND_FILEINFO, SQLHelper.TABLE_FUND_RISKINFO, threerating, fiverating,  beta, count))
        info = cur.fetchall()
        cur.close()
        return info
        

    def close(self):
        self.conn.close()

